SqlLoader

一. 概述

1.1 常见的几种数据迁移方式

日常开发中会经常遇到这样的需求，大批量的数据需要在不同的数据库间迁移，一般会有如下几种方式（不一定涵盖所有）：

• 源数据库直接导出SQL文件，目标数据库执行；
  • 数据量大时效率很低，甚至可能会卡死客户端。
• 创建数据库间的DBLink，用 create table B as select * from A@dblink where ... ，或 insert into B select * from A@dblink where ... ；
• exp A 表，再 imp 到 B 表，exp 时可加查询条件；
• 程序实现 select from A .. ，然后 insert into B ... ，也要分批提交；
• SqlLoader导入数据。

1.2 什么是SqlLoader

Oracle提供了 sqlldr 命令，参数：

• userid：username/password[@servicename]；
• control：控制文件，可能包含表的数据；
• log：记录导入时的日志文件，默认为 控制文件(去除扩展名).log；
• bad：坏数据文件，默认为 控制文件(去除扩展名).bad；
• data：数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作；
• errors：允许的错误记录数，可以用他来控制一条记录都不能错；
• rows：多少条记录提交一次，默认为 64；
• skip：跳过的行数，比如导出的数据文件前面几行是表头或其他描述。

sqlldr是在处理大数据量的操作中建议采用的方式，它有许多性能相关的开关，能最大程度的减少 redo，undo 的生成。

MySql中对应的语句：load data infile 。

1.3 SqlLoader用法

控制文件 .ctl 格式：

-- 1. 控制文件标识
LOAD DATA   

-- 2. 要导入的数据文件名
INFILE 'xxx.txt'      

-- 3. 将文件插入到数据库的 test 表中
INSERT INTO TABLE test   

-- 4. 用于分割一行中各个属性值的符号（例如每个属性值用逗号分割， 那么就把 X'09' 改为','）
Fields terminated by X'09' 

-- 5. test表中对应的属性名
(id, username, passwprd, ......)    

操作类型有：

1. insert：为缺省方式，在数据装载开始时要求表为空；
2. append：在表中追加新记录；
3. replace：删除旧记录(用 delete from table 语句)，替换成新装载的记录；
4. truncate：删除旧记录(用 truncate table 语句)，替换成新装载的记录。

案例：

load data 

-- 此文件格式是.log，用文本文件的方法可以导入进去
infile 'D:/xxx/xxx.log' 

-- insert为缺省方式，插入时要求表为空
insert into table database_name

-- 数据文件每个字段也就是属性是按照|来分割的
fields terminated by '|'  

-- 数据中某些属性可能是null值 ， 如果不加入这行导入到数据库的时候就会自动跳过null值的列, 这样数据插入后就会属性和值对应不上
trailing nullcols 

-- content运用的类型是clob，当使用sqlldr加载很长的字符串(超过4000)到表中的clob类型中时会报错：数据文件的字段超出最大长度；sqlldr每次读入文件中数据流的数据类型默认为CHAR ,长度为 255 .所以只要超过255字符的段都会报这个错，所以char(10000)用来控制上限
(time, user_id, type, longitude, latitude, height, content char(10000))

接下来可以直接通过 sqlldr 语句导入，一般项目会通过一个数据文件和一个控制文件来实现此功能，这样可以将模板和数据分离，可以重复使用：

• 数据文件：可以是PL/SQL或Toad导出、SqlPlus的Spool格式化产出、UTL_FILE包生成。

1.4 LOAD DATA INFILE用法

LOAD DATA INFILE 是 SELECT ... INTO OUTFILE 的相对语句。把表的数据备份到文件使用 SELECT ... INTO OUTFILE ，从备份文件恢复表数据，使用 LOAD DATA INFILE 。

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

示例：

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl_name 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n'

-- 只载入部分列
LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2)

参数：

• LOW_PRIORITY：适用于表锁存储引擎，比如MyISAM, MEMORY, 和 MERGE，在写入过程中如果有客户端程序读表，写入将会延后，直至没有任何客户端程序读表再继续写入。

• CONCURRENT：使用该参数，允许在写入过程中其它客户端程序读取表内容。

• LOCAL：关键字影响数据文件定位和错误处理，影响在哪里找到数据文件。只有当 mysql-server 和 mysql-client 同时在配置中指定允许使用，LOCAL关键字才会生效。

  • LOCAL load operations：如果指定了LOCAL，数据文将被客户端程序从客户端主机读取，然后发送给服务器主机。将在服务器主机的临时目录创建一个数据文件的副本，如果临时目录剩余空间不足，将导致语句执行失败。

  • Non-LOCAL load operations：如果没有指定LOCAL，数据文件必须位于服务器值机上，直接由 mysql-server 读取。mysql-serve 使用如下规则来定位文件：

    1. 数据文件使用绝对路径，直接使用
    
    2. 数据文件使用相对路径并且有前导的部分，将相对于 mysql-server 的数据目录查找，例如 ./myfile.txt
    
    3. 数据文件使用相对路径并且没有前导的部分，将相对于默认数据库的数据文件目录查找,例如 myfile.txt

    根据上面的规则，./myfile.txt 将被定位到 mysql-server 的 data directory,而 myfile.txt 将被定位到 default database 的 database directory。

    如果 db1 时默认数据库，则下面的语句将从 db1 的数据库目录读取 data.txt,即使明确指定把数据装载到 db2：

    LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

    Non-LOCAL load operations 从服务器主机上读取数据文将。出于安全原因，这个操作需要文件权限。 如果secure_file_priv 系统变量的value 不为空，数据文件必须放在该变量指定的目录。如果该变量为空，数据文件必须可读。

    使用LOCAL将比让服务器直接存取文件慢些，因为文件的内容必须从客户主机传送到服务器主机。在另一方面，你不需要file权限装载本地文件。

  • LOCAL关键字对错误处理的影响：

    1. 使用 LOAD DATA INFILE,data-interpretation 和 duplicate-key 错误会终止操作；
    2. 使用 LOAD DATA LOCAL INFILE，data-interpretation 和 duplicate-key 错误会发出警告，操作将继续执行。对于duplicate-key错误，效果和指定了 IGNORE 关键字一样。

• REPLACE | IGNORE：控制对现有的唯一键记录的重复的处理。

  • 指定REPLACE，新行将代替有相同的唯一键值的现有行。
  • 指定IGNORE，跳过有唯一键的现有行的重复行的输入。
  • 不指定任何一个选项，当找到重复键键时，出现一个错误，并且文本文件的余下部分被忽略时。

• FIELDS：

  • 基本用法：如果指定一个FIELDS子句，它的每一个子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY)也是可选的，但是必须至少指定一个。

    如果你指定 FIELDS 或 LINES ，缺省值为：

    FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
    LINES TERMINATED BY '\n' STARTING BY ''

    缺省值导致读取输入时，LOAD DATA INFILE表现如下：

    1. 在 \n 处寻找行边界
    2. 在 \t 处将行分进字段
    3. 不要期望字段由任何引号字符封装
    4. 将由“\”开头的定位符、换行符或“\”解释成转义序列。例如 \t, \n, and \ 分别解释成 定位符，换行，反斜杠。

    相反，缺省值导致在写入输出时，SELECT … INTO OUTFILE表现如下：

    1. 在字段之间写 \t
    2. 不用任何引号字符封装字段
    3. 使用“\”转义出现在字段中的 定位符、换行符或“\”字符
    4. 在行尾处写换行符

  • FIELDS [OPTIONALLY] ENCLOSED BY：控制哪些字段应该包裹在引号里面。

    对于SELECT … INTO OUTFILE 输出，如果不包含OPTIONALLY选项，所有的字段将会被ENCLOSED BY指定的字符包裹，例如：

    "1","a string","100.20"  
    "2","a string containing a , comma","102.20"  
    "3","a string containing a \" quote","102.20"  
    "4","a string containing a \", quote and comma","102.20"  

    如果我们指定OPTIONALLY，只有string数据类型（如 CHAR, BINARY, TEXT, 或 ENUM）的字段才会被ENCLOSED BY指定的字符包裹，例如：

    1,"a string",100.20  
    2,"a string containing a , comma",102.20  
    3,"a string containing a \" quote",102.20  
    4,"a string containing a \", quote and comma",102.20  

    注意，如果在字段值内出现ENCLOSED BY字符，则通过使用ESCAPED BY字符作为前缀，对ENCLOSED BY字符进行转义。另外，要注意，如果指定了一个空ESCAPED BY值，则可能会生成不能被LOAD DATAINFILE 正确读取的输出值。例如：

    1,"a string",100.20  
    2,"a string containing a , comma",102.20  
    3,"a string containing a " quote",102.20  
    4,"a string containing a ", quote and comma",102.20 

  • FIELDS ESCAPED BY：用来控制如何对特殊字符进行读写。

    如上面一个例子，导出和导入时指定FIELDS ESCAPED BY为双引号[“]才能被正确的导入，导出的格式如下，对字段内的双引号[“]进行了转义。

    1,"a string",100.20  
    2,"a string containing a , comma",102.20  
    3,"a string containing a #" quote",102.20  
    4,"a string containing a #", quote and comma",102.20  

    对于输入：
    假如FIELDS ESCAPED BY指定字符非空，则输入时该字符被移除，后续的内容被添加到字段里。一些两个字符的字符串序列且第一个字符是转义字符的例外 。这些字符序列见下表：

    Character	Escape Sequence
    \0	An ASCII NUL (X’00’) character
    \b	A backspace character
    \n	A newline (linefeed) character
    \r	A carriage return character
    \t	A tab character.
    \Z	ASCII 26 (Control+Z)
    \N	NULL

    假如FIELDS ESCAPED BY指定字符为空，将不会发生转义序列的解释。

    对于输出，如果FIELDS ESCAPED BY指定字符非空，字符作为以下输出的前缀：

    1. FIELDS ESCAPED BY 字符，例如 \
    2. FIELDS [OPTIONALLY] ENCLOSED BY 字符，例如 “
    3. FIELDS TERMINATED BY and LINES TERMINATED BY 的 value 的第一个字符,例如\n
    4. ASCII 0

    如果LINES TERMINATED BY是空字符串，FIELDS TERMINATED BY非空,字符将不会被转义，NULL 将输出为 NULL而不是 \N。指定LINES TERMINATED BY为空字符串并不是个好主意，特别当内容中包含上表列出的特殊字符时。

  • LINES STARTING BY：如果所有希望读入的行都含有一个我们希望忽略的共用前缀，则可以使用 LINES STARTING BY ‘prefix_string’ 来跳过前缀（以及该前缀前的所有字符）。如果某行不包括前缀，则整个行被跳过。

    LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test 
       FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

    假如/tmp/test.txt文件内容如下：

    xxx"abc",1  
    something xxx"def",2  
    "ghi",3  

    则我们读入的内容包括(“abc”,1) 和 (“def”,2)，第三行直接被跳过。

  • IGNORE number LINES：该选项可以被用于在文件的开始处忽略行。例如，我们可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行。

    LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

  • 在特定情况下，field-和line-handling选项相互影响：

    1. 如果LINES TERMINATED BY是空字符串，FIELDS TERMINATED BY非空,行以FIELDS TERMINATED BY指定的字符串作为结尾。
    2. 如果FIELDS TERMINATED BY 与 FIELDS ENCLOSED BY值均为空(‘’)，将使用固定行（无分割）格式。使用固定行格式，字段之间将没有分隔符（行终止符依然可使用），列字段数据的读取和写入均按照字段定义的宽度去操作，如 TINYINT, SMALLINT, MEDIUMINT, INT, 和 BIGINT, 字段宽度分别为4, 6, 8, 11, 和 20。

  • 不适合使用 LOAD DATA INFILE 的情况：

    1. 使用固定行格式（即FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 均为空）,列字段类型为BLOB或TEXT。
    2. 指定分隔符与其它选项前缀一样，LOAD DATA INFILE不能对输入做正确的解释。例如：
       FIELDS TERMINATED BY '"' ENCLOSED BY '"'  
    3. 如果 FIELDS ESCAPED BY 为空，字段值包含 FIELDS ENCLOSED BY 指定字符，或者 LINES TERMINATED BY 的字符在 FIELDS TERMINATED BY 之前，都会导致过早的停止 LOAD DATA INFILE操作。因为LOAD DATA INFILE不能准确的确定行或列的结束。

• IGNORE number LINES 选项：IGNORE number LINES选项可以被用于在文件的开始处忽略行。
  您可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行：

  LOAD DATA INFILE '/tmp/test.txt'  INTO TABLE test IGNORE 1 LINES;

• 选择导入的列：

  下面的语句会导入文件的所有列

  LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

  如果我们想导入表的某些列，需要指定列的列表

  LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

  如果输入文件与表的列顺序不同，我们必须指定一个列清单，否则mysql不能把输入文件的字段与表的列匹配起来。

  mysql> LOAD DATA INFILE '/tmp/loadtest.txt' INTO TABLE loadtest 
     FIELDS TERMINATED BY ',' 
     OPTIONALLY ENCLOSED BY '"' 
     ESCAPED BY '#' 
     LINES TERMINATED BY '\n' 
     (c1, c2, c4, c3);  

  列的清单可以包含列名或者用户变量，在写入列前我们需要使用SET语句对用户变量进行转换。对set语句及用户变量有如下使用方法：

  方法1：在用户变量用于第一列之前，先把第一列的值赋予用户变量，进行除法操作后输入到c1。

  LOAD DATA INFILE 'file.txt'
    INTO TABLE t1
    (column1, @var1)
    SET column2 = @var1/100;

  方法2The SET clause can be used to supply values not derived from the input file. 。使用下面例子把c3列设为当前时间

  LOAD DATA INFILE 'file.txt'
    INTO TABLE t1
    (column1, column2)
    SET column3 = CURRENT_TIMESTAMP;

  方法3：把输入赋予用户变量，而不把用户变量赋予表中的列，来丢弃此输入值。

  LOAD DATA INFILE 'file.txt'
    INTO TABLE t1
    (column1, @dummy, column2, @dummy, column3);

• 通过管道导入数据：

  在unix系统中，如果我们想要从管道（pipe）中load data，需要用如下方法：
  [root@localhost tmp]# mkfifo /tmp/ls.dat  
  [root@localhost tmp]# chmod 666 /tmp/ls.dat  
  [root@localhost tmp]# find / -ls > /tmp/ls.dat &  
  [root@localhost tmp]# mysql -e "LOAD DATA INFILE '/tmp/ls.dat' INTO TABLE test.tb1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '#' LINES TERMINATED BY '\n'"  

  注意：sql-mode使用严格事物模式STRICT_TRANS_TABLES会报错

  在另一窗口将数据写入管道

  [root@localhost /]# cat /tmp/loadtest.txt > /tmp/ls.dat  

  注：可以先读或者先写管道，谁先谁后都可以，在写入管道的数据被全部读出前，处于阻塞状态。

二. 实战

2.1 导出SQL

导出一千万条数据，耗时-5分钟，sql文件大小-1.17 GB

直接执行SQL文件，耗时54分41秒

2.2 load data infile

1.在源数据库导出数据文件：

select * from logs1 into outfile 'logs1.txt';

执行耗时：（只需半分钟）

文件大小：676MB

生成文件格式：

1811	1	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:00	首页
1812	2	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:01	首页
1813	1	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:02	首页
1814	1	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:03	首页
1815	1	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:04	首页
......

3.目标数据库执行命令：（需要先导入表结构）

load data infile 'logs1.txt' into table logs1(id, logtype, logurl, logip, logdz, ladduser, lfadduser, laddtime, htmlname);

执行耗时：（只需5分钟左右）

对比导出SQL文件，耗时从将近1小时缩短到5分钟。

2.3 SqlLoader

1.导出数据文件，可以是任意支持格式，如 .txt，.csv 等。

2.创建控制文件：

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行  
LOAD DATA  
INFILE "users_data.csv" --指定外部数据文件，可以写多 个 INFILE "another_data_file.csv" 指定多个数据文件  
--这里还可以使 用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，  
truncate --操作类型，用 truncate table 来清除表中原有 记录  
INTO TABLE users -- 要插入记录的表  
Fields terminated by "," -- 数据中每行记录用 "," 分隔  
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时  
trailing nullcols --表的字段没有对应的值时允 许为空  
(  
  virtual_column FILLER, --这是一个虚拟字段，用来跳 过由 PL/SQL Developer 生成的第一列序号  
  user_id number, --字段可以指定类型，否则认 为是 CHARACTER 类型, log 文件中有显示  
  user_name,  
  login_times,  
  last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换  
) 

执行完 sqlldr 后可以留意一下生成的几个文件，如 users.log 日志文件、users.bad 坏数据文件等。特别是要看看日志文件，从中可让你更好的理解 Sql Loader，里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息。

三. SqlLoader细节

（1）SqlLoader 的性能与并发操作

1. ROWS 的默认值为 64，可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。

2. 常规导入可以通过使用 INSERT语句来导入数据。Direct 导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中，可以提高导入数据的性能。当然，在很多情况下，不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE!)。

3. 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志(是否要 alter table table1 nologging 呢?)。这个选项只能和 direct 一起使用。

4. 对于超大数据文件的导入就要用并发操作了，即同时运行多个导入任务。

   sqlldr userid=/ control=result1.ctl direct=true parallel=true   
   sqlldr userid=/ control=result2.ctl direct=true parallel=true   
   sqlldr userid=/ control=result2.ctl direct=true parallel=true 

5. 当加载大量数据时（大约超过10GB），最好抑制日志的产生： SQL>ALTER TABLE RESULTXT nologging; 这样不产生REDO LOG，可以提高效率。然后在 CONTROL 文件中 load data 上面加一行：unrecoverable， 此选项必须要与DIRECT共同应用。

6. 在并发操作时，ORACLE声称可以达到每小时处理100GB数据的能力！其实，估计能到 1－10G 就算不错了，开始可用结构相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。（注意：一般只能用ASCII码形式，切记要转换编码，不然导入数据为空）

（2）批量插入性能优化

结论总结：

1. 使用常规路径插入数据时，可通过参数ROWS来增加批量提交的数量来增加插入速度，但ROWS受bindsize影响，例如每行大小为10字节，ROWS=10000，bindsize=50000，则批次提交大小为10W字节，但是bindsize只有5W，所以批次提交数量实际为5000
2. 常规路径插入时如果控制文件和数据文件是同一个文件，则ROWS，bindsize不能完全起作用
3. 使用直接路径插入数据，当接口表为空时会比使用常规路径快大约3 ~ 4倍。但是随着接口表历史数据的不断增加，逐渐减少为只能快1 ~ 2倍左右。主要原因在于需要维护索引。并且使用直接路径插入时大文件比小文件拥有更大的优势。
4. 不管使用直接路径还是常规路径随着接口表的数据增大均会影响两种模式的插入速度，主要原因在于索引维护。
5. 直接路径写入时大文件比小文件拥有更大的优势。常规路径则相差不大。
6. 使用常规路径，开启多个SQLLODER并不会变快反而可能比单个SQLLOADER慢，主要原因是受制于测试机的磁盘IO以及索引的维护。
7. 直接路径不能应用于并发环境，且使用直接路径插入时需要考虑对表高水位的清理。
8. 最后额外提一句，注意你的REDO LOG的设置不会太小。

---

参考：

🔗 oracle sqlldr 用法详解

🔗 MySql官方文档-LOAD DATA Statement

🔗 LOAD DATA INFILE 导入数据

🔗 SQL*Loader批量插入性能优化